package cn.ztydata.teachingsystem.heaven.dao;

import cn.ztydata.teachingsystem.common.Page;
import cn.ztydata.teachingsystem.heaven.entity.Department;
import cn.ztydata.teachingsystem.heaven.entity.DeptStatistics;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * 系部Dao
 *
 * @author fanruiping
 * @since 2014-11-28
 */
@Repository
public class DepartmentDao extends BaseDao {
    /**
     * 新增系部
     * @param entity 实体类
     *
     * @author fanruiping
     * @since 2014-11-28
     */
    public void add(Department entity){
        String sql = "INSERT INTO t_department(sNumber,sName,sIntro) VALUES(:number,:name,:intro)";

        MapSqlParameterSource params = new MapSqlParameterSource().addValue("number",entity.getNumber()).addValue("name",entity.getName())
                .addValue("intro",entity.getIntro());

        namedParameterJdbcTemplate.update(sql, params);
    }

    /**
     * 更新系部数据
     * @param entity 实体类
     * @return boolean
     *
     * @author fanruiping
     * @since 2014-11-28
     */
    public boolean modify(Department entity){
        String sql = "UPDATE t_department SET sNumber=:number,sName=:name,sIntro=:intro WHERE id=:id";

        MapSqlParameterSource params = new MapSqlParameterSource().addValue("id", entity.getId()).addValue("number", entity.getNumber())
                .addValue("name", entity.getName()).addValue("intro", entity.getIntro());

        log.debug("update department info");

        return namedParameterJdbcTemplate.update(sql, params) == 1;
    }

    /**
     * 删除指定系部数据
     * @param id 系部编号
     *
     * @author fanruiping
     * @since 2014-11-28
     */
    public int delete(long id){
        String sql = "DELETE FROM t_department WHERE id=:id";

        MapSqlParameterSource params = new MapSqlParameterSource().addValue("id", id);

        log.debug("删除指定系部");

        return namedParameterJdbcTemplate.update(sql, params);
    }

    /**
     * 根据系部编码获取系部详细
     * @param number 系部编码
     * @return Department
     *
     * @author fanruiping
     * @since 2014-12-01
     */
    public Department getByNumber(String number){
        try{
            String sql = "SELECT id,sNumber,sName,sIntro FROM t_department WHERE sNumber=:number";

            MapSqlParameterSource params = new MapSqlParameterSource().addValue("number", number);

            log.debug("return department detail by number");

            return namedParameterJdbcTemplate.queryForObject(sql, params, CreateMapper());
        }catch (EmptyResultDataAccessException ex){
            log.debug("catch EmptyResultDataAccessException:return null");

            return null;
        }
    }

    /**
     * 根据系部编码和编号获取系部详细
     * @param number 系部编码
     * @param id 系部编号
     * @return Department
     *
     * @author fanruiping
     * @since 2014-12-01
     */
    public Department getByNumberAndId(String number, long id){
        try {
            String sql = "SELECT id,sNumber,sName,sIntro FROM t_department WHERE sNumber=:number AND id!=:id LIMIT 1";

            MapSqlParameterSource params = new MapSqlParameterSource().addValue("number", number).addValue("id", id);

            log.debug("return department detail by number and id");

            return namedParameterJdbcTemplate.queryForObject(sql, params, CreateMapper());
        }catch (EmptyResultDataAccessException ex){
            log.debug("catch EmptyResultDataAccessException:return null");

            return null;
        }
    }

    /**
     * 根据系部编码获取详细信息
     * @param id 编号
     * @return Department
     *
     * @author fanruiping
     * @since 2014-11-29
     */
    public Department getById(long id){
        try {
            String sql = "SELECT id,sNumber,sName,sIntro FROM t_department WHERE id=:id";

            MapSqlParameterSource params = new MapSqlParameterSource().addValue("id", id);

            log.debug("return department detail by id");

            return namedParameterJdbcTemplate.queryForObject(sql, params, CreateMapper());
        }catch (EmptyResultDataAccessException ex){
            log.debug("catch EmptyResultDataAccessException:return null");

            return null;
        }
    }

    /**
     * 获取系部列表
     * @param pageNum 当前页
     * @param pageSize 每页显示条数
     * @return Page
     *
     * @author fanruiping
     * @since 2014-11-28
     */
    public Page<Department> getList(int pageNum, int pageSize){
        String totalSql = "SELECT COUNT(*) FROM t_department LIMIT 1";
        String dataSql = "SELECT id,sNumber,sName,sIntro FROM t_department LIMIT :pageNum,:pageSize";

        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue("pageNum", (pageNum - 1) * pageSize);
        params.addValue("pageSize", pageSize);

        int total = namedParameterJdbcTemplate.queryForObject(totalSql, params, Integer.class);

        if(total > 0){
            log.debug("return department list");

            List<Department> entities = namedParameterJdbcTemplate.query(dataSql, params, CreateMapper());

            return new Page<>(total,pageSize,pageNum,entities);
        }

        log.debug("return empty page");

        return new Page<>();
    }

    /**
     * 根据编码获取系部名称集合
     * @param numbers 系部编码
     * @return List
     * @author fanruiping
     * @since 2015-05-05
     */
    public Map<String, String> getNamesByNumbers(List<String> numbers){
        final Map<String, String> result = Maps.newHashMap();

        String sql = "SELECT sNumber,sName FROM t_department WHERE sNumber IN(:numbers)";

        namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("numbers", numbers), new RowMapper<Object>() {
            @Override
            public Object mapRow(ResultSet rs, int i) throws SQLException {
                result.put(rs.getString("sNumber"), rs.getString("sName"));

                return null;
            }
        });

        return result;
    }

    /**
     * 根据编码获取系部集合
     * @param numbers 系部编码
     * @return List
     * @author fanruiping
     * @since 2015-05-13
     */
    public List<Department> getByNumbers(List<String> numbers){
        String sql = "SELECT id,sNumber,sName,sIntro FROM t_department WHERE sNumber IN(:numbers)";

        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("numbers", numbers), CreateMapper());
    }

    /**
     * 获取所有系部
     * @return List
     *
     * @author fanruiping
     * @since 2014-12-02
     */
    public List<Department> getAll(){
        String sql = "SELECT id,sName,sNumber FROM t_department";

        log.debug("return department list");

        return namedParameterJdbcTemplate.query(sql,CreateSimpleMapper());
    }

    /**
     * 获取所有的系部ID和名称
     * @return List
     *
     * @author cx
     * @since 2014-12-01
     */
    public List<DeptStatistics> getAllForName() {
        String sql = "SELECT id,sName,sNumber FROM t_department";

        log.debug("获取所有的系部ID和名称");
        return namedParameterJdbcTemplate.query(sql, new RowMapper<DeptStatistics>() {
            @Override
            public DeptStatistics mapRow(ResultSet resultSet, int i) throws SQLException {
                DeptStatistics entity = new DeptStatistics();

                entity.setId(resultSet.getLong("id"));
                entity.setName(resultSet.getString("sName"));
                entity.setNumber(resultSet.getString("sNumber"));

                log.debug("系部ID和名称映射到实体类");
                return entity;
            }
        });
    }

    /**
     * 批量新增系部
     * @param deptList 系部信息集合
     * @return int[]
     *
     * @author cx
     * @since 2014-11-29
     */
    public int[] saveDepts(final List<Department> deptList) {
        String sql = "INSERT INTO t_department(sName,sNumber,sIntro) VALUES(:name, :number, :intro)";

        SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(deptList.toArray());

        log.debug("批量保存导入的系部信息");
        return namedParameterJdbcTemplate.batchUpdate(sql, batch);
    }

    /**
     * 获取与数据库中重复的系部编码
     * @param deptNumSet 系部编码集合
     * @return String
     *
     * @author cx
     * @since 2014-12-04
     */
    public String getDuplicateNum (final Set<String> deptNumSet) {
        if (deptNumSet.isEmpty()) {
            log.debug("导入系部编码集合为0:deptNumSet.isEmpty()");

            log.info("系部编号集合为0");
            return null;
        }

        try {
            String sql = "SELECT sNumber FROM t_department WHERE sNumber IN (:numSet) LIMIT 1";
            SqlParameterSource sps = new MapSqlParameterSource().addValue("numSet", deptNumSet);

            log.debug("return 导入的系部与表记录重复的编码");
            return namedParameterJdbcTemplate.queryForObject(sql, sps, String.class);
        } catch (EmptyResultDataAccessException ex) {
            log.debug("导入的系部与表记录无重复编码");

            log.info("数据库中无重复的系部编码");
            return null;
        }
    }

    /**
     * 根据专业编号获取系部编号
     * @param specialtyId 专业编号
     * @return int
     *
     * @author fanruiping
     * @since 2014-12-18
     */
    public Long getIdBySpecId(Long specialtyId){
        try {
            String sql = "SELECT id FROM t_department WHERE sNumber IN(SELECT sDeptNum FROM t_specialty WHERE id=:id)";

            log.debug("根据专业编号获取系部编号");
            return namedParameterJdbcTemplate.queryForObject(sql, new MapSqlParameterSource().addValue("id", specialtyId), Long.class);
        }catch (EmptyResultDataAccessException ex){
            long id = 0;

            log.debug("根据专业编号获取系部编号失败");
            return id;
        }
    }

    /**
     * 根据系部编号集合获取系部编号
     * @param ids 编号集合
     * @return int
     *
     * @author fanruiping
     * @since 2014-12-29
     */
    public List<Department> getByIds(Set<Long> ids){
        if (ids == null || ids.isEmpty()) {
            return Lists.newArrayList();
        }

        String sql = "SELECT * FROM t_department WHERE id IN(:ids)";

        log.debug("根据编号集合返回系部信息集合");

        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("ids", ids), CreateMapper());
    }

    /**
     * 获取多个系部信息
     *
     * @author chenxin
     * @since 2015/1/12
     */
    public List<Department> getByLimit(final int limit) {
        String sql = "SELECT id,sName,sNumber FROM t_department LIMIT :limit";

        log.debug("获取多个系部信息");
        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("limit", limit), new RowMapper<Department>() {
            @Override
            public Department mapRow(ResultSet rs, int rowNum) throws SQLException {
                Department entity = new Department();

                entity.setId(rs.getLong("id"));
                entity.setName(rs.getString("sName"));
                entity.setNumber(rs.getString("sNumber"));

                return entity;
            }
        });
    }

    /**
     * ResultSet转换为专业实体类
     * @return RowMapper
     *
     * @author fanruiping
     * @since 2014-11-28
     */
    private RowMapper<Department> CreateMapper(){
        return new RowMapper<Department>() {
            @Override
            public Department mapRow(ResultSet rs, int i) throws SQLException {
                Department entity = new Department();

                entity.setId(rs.getLong("id"));
                entity.setNumber(rs.getString("sNumber"));
                entity.setName(rs.getString("sName"));
                entity.setIntro(rs.getString("sIntro"));

                return entity;
            }
        };
    }

    /**
     * ResultSet转换为专业实体类（部分数据）
     * @return RowMapper
     *
     * @author fanruiping
     * @since 2014-11-28
     */
    private RowMapper<Department> CreateSimpleMapper(){
        return new RowMapper<Department>() {
            @Override
            public Department mapRow(ResultSet rs, int i) throws SQLException {
                Department entity = new Department();

                entity.setId(rs.getLong("id"));
                entity.setNumber(rs.getString("sNumber"));
                entity.setName(rs.getString("sName"));

                return entity;
            }
        };
    }

    /**
     * 获取系部列表
     * @param
     * @return List
     * @author lyj
     * @since 2015-2-4
     */
    public List<Map<String,Object>> getDepartExercises(List<Long> deptIds, List<Long> specIds, int isOpen, int status, long creatorId, int tag, String search){
        StringBuilder sql = new StringBuilder("SELECT dept.id,dept.sNumber AS code,dept.sName AS name,COUNT(exer.id) AS count FROM t_department dept LEFT JOIN t_exercise exer ON dept.id=exer.iDeptId ");
        MapSqlParameterSource param = new MapSqlParameterSource();

        //判断是否公开
        if (isOpen >= 0 && isOpen != 9){
            log.debug("判断是否公开：isOpen >= 0 && isOpen != 9");
            sql.append(" AND exer.tiOpen = :tiOpen ");
            param.addValue("tiOpen", isOpen);
        }

        //判断状态
        if (status >= 0 && status != 9){
            log.debug("判断状态：status >= 0 && status != 9");
            sql.append(" AND exer.tiStatus = :tiStatus");
            param.addValue("tiStatus", status);
        }
        //判断创建者/审批人
        if (creatorId > 0) {
            if (tag == 3) {
                log.debug("判断审批人大于0：creatorId > 0");
                sql.append(" AND exer.iAuditorId = :iAuditorId");
                param.addValue("iAuditorId", creatorId);

            } else {
                log.debug("判断创建者大于0：creatorId > 0");
                sql.append(" AND exer.iCreatorId = :iCreatorId");
                param.addValue("iCreatorId", creatorId);

            }
        }

        //判断系部
        if (deptIds.size() > 0){
            log.debug("部门ID的List不为空：deptIds.size() > 0");
            sql.append(" AND dept.id IN (:deptIds)");
            param.addValue("deptIds", deptIds);
        }

        //判断专业
        if (specIds.size() > 0){
            log.debug("专业ID的List不为空：specIds.size() > 0");
            sql.append(" AND exer.iSpecId IN (:specIds)");
            param.addValue("specIds", specIds);
        }

        //根据关键字模糊搜索
        if (!search.isEmpty()){
            log.debug("根据关键字模糊搜索:!search.isEmpty()");
            sql.append(" AND UPPER(exer.sTopic) LIKE :search");
            param.addValue("search", "%" + search.toUpperCase() + "%");
        }

        sql.append(" GROUP BY dept.id ORDER BY count DESC ");

        log.debug("查询系部习题列表");
        return  namedParameterJdbcTemplate.queryForList(sql.toString(), param);
    }


    /**
     * 根据专业ID获取系部ID
     *
     * @param specIds 专业ID集合
     * @return
     * @author lyj
     * @since 2015-2-13
     */
    public List<Long> getDepartIds(List<Long> specIds){
        if (specIds.size() < 1){
            log.debug("专业ID的List元素个数为0返回new ArrayList：specIds.size() < 1");
            return new ArrayList<>();
        }

        String sql = "SELECT id FROM t_department WHERE sNumber IN (SELECT sDeptNum FROM t_specialty WHERE id IN (:specId))";

        MapSqlParameterSource param = new MapSqlParameterSource().addValue("specId", specIds);

        log.debug("根据专业ID获取系部ID");
        return namedParameterJdbcTemplate.queryForList(sql, param, Long.class);
    }

    /**
     * 根据编号集合获取系部代码集合
     * @param ids 编号集合
     * @return List
     *
     * @author fanruiping
     * @since 2015-05-13
     */
    public List<String> getNumberByIds(Set<Long> ids){
        String sql = "SELECT sNumber FROM t_department WHERE id IN(:ids)";

        return namedParameterJdbcTemplate.queryForList(sql,new MapSqlParameterSource().addValue("ids",ids), String.class);
    }
}
